package com.java.dao.impl;

import com.java.bean.Express;
import com.java.dao.BaseExpressDao;
import com.java.exception.DuplicateCodeException;
import com.java.util.DBUtil;

import java.sql.SQLException;
import java.sql.Statement;
import java.util.*;

public class ExpressDaoMysql extends DBUtil implements BaseExpressDao {

    public static final String SQL_CONSOLE =                    "SELECT COUNT(1) SIZE1,COUNT(TO_DAYS(INTIME)=TO_DAYS(NOW()) OR NULL) DAY1,COUNT(OUTTIME IS NULL OR NULL) SIZE2,COUNT(OUTTIME IS NULL AND TO_DAYS(INTIME)=TO_DAYS(NOW()) OR NULL) DAY2 FROM express";// language=MySQL
    public static final String SQL_MAP =                        "SELECT ADDR K,SUM(DISPATCHES) V FROM couriers WHERE NOT ADDR IS NULL AND NOT STATUS=1 GROUP BY K;";  // language=MySQL
    public static final String SQL_LIST_EXPRESS =               "SELECT * FROM express LIMIT ?;           ";    // language=MySQL
    public static final String SQL_LIST_EXPRESS_LIMIT =         "SELECT * FROM express LIMIT ?,?;         ";    // language=MySQL
    public static final String SQL_GET_EXPRESS_BY_NUMBER =      "SELECT * FROM express WHERE NUMBER=?;    ";    // language=MySQL
    public static final String SQL_GET_EXPRESS_BY_CODE =        "SELECT * FROM express WHERE CODE=?;      ";    // language=MySQL
    public static final String SQL_GET_EXPRESS_BY_USERPHONE =   "SELECT * FROM express WHERE USERPHONE=?; ";    // language=MySQL
    public static final String SQL_GET_EXPRESS_BY_CPHONE =      "SELECT * FROM express WHERE SYSPHONE=?;  ";    // language=MySQL
    public static final String SQL_GET_EXPRESS_BY_ID =          "SELECT * FROM express WHERE id=?;       ";    // language=MySQL
    public static final String SQL_GET_RANK_OF_COUNT_BY_PHONE = "SELECT USERPHONE,COUNT(1) FROM express GROUP BY USERPHONE ORDER BY COUNT(1) DESC LIMIT 15";// language=MySQL
    public static final String SQL_GET_RANK_RANGE =             "SELECT USERPHONE,COUNT(1) FROM express WHERE INTIME BETWEEN ? AND ? GROUP BY USERPHONE ORDER BY COUNT(1) DESC LIMIT 15";
    // language=MySQL
    public static final String SQL_INSERT_EXPRESS =             "INSERT INTO express VALUES(NULL,?,?,?,DEFAULT,NULL,DEFAULT,?,?,?)";// language=MySQL
    public static final String SQL_INSERT_EXPRESS_WITH_INTIME = "INSERT INTO express(NUMBER,COMPANY,CODE,USERNAME,SYSPHONE,USERPHONE,INTIME) VALUES(?,?,?,?,?,?,?)";// language=MySQL
    public static final String SQL_UPDATE =                     "UPDATE express SET NUMBER=?,COMPANY=?,USERNAME=?,SYSPHONE=?,CODE=?,OUTTIME=NULL,ESTATUS=0 WHERE id=?";// language=MySQL
    public static final String SQL_UPDATE_WITH_STATE =          "UPDATE express SET NUMBER=?,COMPANY=?,USERNAME=?,SYSPHONE=?,CODE=NULL,OUTTIME=NOW(),ESTATUS=1 WHERE id=?";// language=MySQL
    public static final String SQL_DELETE =                     "DELETE FROM express WHERE id=?";// language=MySQL
    public static final String SQL_UPDATE_STATE =               "UPDATE express SET CODE=NULL,OUTTIME=NOW(),ESTATUS=1 WHERE CODE=?";// language=MySQL
    public static final String SQL_GET_EXPRESS_BY_USERPHONE_AND_STATUS = "SELECT * FROM express WHERE USERPHONE=? AND ESTATUS=?";

    /**
     * 获取控制台所需的快递数据  ->  总快件数  待取件数
     *
     * @return [{size:XXX,day:XXX},{size:XXX,day:XXX}]
     */
    @Override
    public List<Map<String, Integer>> console() {
        try {
            rs = query(getConnection().prepareStatement(SQL_CONSOLE), null);
            if (rs.next()) {
                // 封装结果
                List<Map<String, Integer>> result = new ArrayList<>();
                Map<String, Integer> data = new HashMap<>();
                data.put("size", rs.getInt("SIZE1"));
                data.put("day", rs.getInt("DAY1"));
                result.add(data);
                data = new HashMap<>();
                data.put("size", rs.getInt("SIZE2"));
                data.put("day", rs.getInt("DAY2"));
                result.add(data);
                return result;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closeAll();
        }
        return null;
    }

    /**
     * 获取快件分布图所需的快递数据
     *
     * @return [{name:XXX,value:XXX},{name:XXX,value:XXX},...]
     */
    @Override
    public List<Map<String, Object>> map() {
        try {
            List<Map<String, Object>> result = new ArrayList<>();
            query(pps(SQL_MAP), null);
            while (rs.next()) {
                Map<String, Object> map = new HashMap<>();
                map.put("name", rs.getString("K"));
                map.put("value", rs.getInt("V"));
                result.add(map);
            }
            return result;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closeAll();
        }
        return null;
    }

    /**
     * 查找快件
     *
     * @param limit    是否分页 0 是， 1 查询所有
     * @param offset   起始索引
     * @param pageSize 分页时， 获取的快递数量
     * @return 快递列表
     */
    @Override
    public List<Express> listExpress(boolean limit, int offset, int pageSize) {
        try {
            List<Object> params = new ArrayList<>();
            if (limit) {
                params.add(offset);
                params.add(pageSize);
                query(getConnection().prepareStatement(SQL_LIST_EXPRESS_LIMIT), params);
            } else {
                params.add(pageSize);
                query(getConnection().prepareStatement(SQL_LIST_EXPRESS), params);
            }
            return setAll(Express.class);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            closeAll();
        }
        return null;
    }

    /**
     * 根据单号查询快递信息
     *
     * @param number 单号
     * @return 快递信息
     */
    @Override
    public Express getExpressByNumber(String number) {
        return getExpress(number, SQL_GET_EXPRESS_BY_NUMBER);
    }

    /**
     * 根据取件码查询快递信息
     *
     * @param code 取件码
     * @return 快递信息
     */
    @Override
    public Express getExpressByCode(String code) {
        return getExpress(code, SQL_GET_EXPRESS_BY_CODE);
    }


    /**
     * 根据用户的手机号，查询快递信息
     *
     * @param userPhone 用户的手机号
     * @return 快递信息
     */
    @Override
    public List<Express> getExpressByUserphone(String userPhone) {
        return getExpresses(userPhone, SQL_GET_EXPRESS_BY_USERPHONE);
    }

    /**
     * 根据用户的手机号，查询快递信息
     *
     * @param userPhone 用户的手机号
     * @return 快递信息
     */
    @Override
    public List<Express> getExpressByUserphoneAndStatus(String userPhone, int status) {
        try {
            List<Object> params = new ArrayList<>();
            params.add(userPhone);
            params.add(status);
            query(getConnection().prepareStatement(SQL_GET_EXPRESS_BY_USERPHONE_AND_STATUS), params);
            return setAll(Express.class);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            closeAll();
        }
        return null;
    }

    /**
     * 根据录入人的手机号，查询快递信息（快递员/柜子的历史记录）
     *
     * @param cphone 录入人的手机号
     * @return 快递信息
     */
    @Override
    public List<Express> getExpressByCphone(String cphone) {
        return getExpresses(cphone, SQL_GET_EXPRESS_BY_CPHONE);
    }

    private Express getExpress(Object oneParam, String sqlGetExpress) {
        try {
            query(getConnection().prepareStatement(sqlGetExpress), oneParam);
            return setOne(Express.class);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            closeAll();
        }
        return null;
    }

    private List<Express> getExpresses(String param, String sqlGetExpresses) {
        List<Object> params = new ArrayList<>();
        try {
            params.add(param);
            query(getConnection().prepareStatement(sqlGetExpresses), params);
            return setAll(Express.class);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            closeAll();
        }
        return null;
    }

    /**
     * 快件录入
     *
     * @param express 必须包含：快递单号  快递公司  取件码  收件人姓名  录入人电话
     * @return 生成的键
     */
    @Override
    public int insert(Express express) throws DuplicateCodeException {
        try {
            String SQL = SQL_INSERT_EXPRESS;
            List<Object> params = new ArrayList<>();
            params.add(express.getNumber());
            params.add(express.getCompany());
            params.add(express.getCode());
            params.add(express.getUsername());
            params.add(express.getSysphone());
            params.add(express.getUserphone());
            if (express.getIntime() != null) {
                params.add(express.getIntime());
                SQL = SQL_INSERT_EXPRESS_WITH_INTIME;
            }
            if (updateExpress(params, SQL, express.getCode()) > 0) {
                try {
                    rs = pps.getGeneratedKeys();
                    return getCount();
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
            return -1;
        } finally {
            closeAll();
        }
    }

    /**
     * 修改快递信息
     *
     * @param id         要修改的快递id
     * @param newExpress 必须包含：快递单号、快递公司、快递的状态、取件码、收件人id、快递员id, 其他元素会被忽略。
     *                   新的快递的状态若为 1（已取件）则新取件码会被忽略。
     * @return 影响行数
     */
    @Override
    public int update(int id, Express newExpress) throws DuplicateCodeException {
        try {
            List<Object> params = new ArrayList<>();
            params.add(newExpress.getNumber());
            params.add(newExpress.getCompany());
            params.add(newExpress.getUsername());
            params.add(newExpress.getSysphone());
            if (newExpress.getEstatus() == 1) {
                params.add(id);
                return updateExpress(params, SQL_UPDATE_WITH_STATE, "");
            } else {
                params.add(newExpress.getCode());
                params.add(id);
                return updateExpress(params, SQL_UPDATE, newExpress.getCode());
            }
        } finally {
            closeAll();
        }
    }

    private int updateExpress(List<Object> params, String sqlUpdate, String code) throws DuplicateCodeException {
        try {
            return update(pps = getConnection().prepareStatement(sqlUpdate, Statement.RETURN_GENERATED_KEYS), params);
        } catch (SQLException exception) {
            if (exception.getMessage().contains("code_u")) {
                throw new DuplicateCodeException("Duplicate code: " + code);
            }
            if (exception.getMessage().contains("number_u")) {
                return -1;
            }
            exception.printStackTrace();
        }
        return -1;
    }

    /**
     * 根据id删除快递信息
     *
     * @param id 要删除的快递id
     * @return 影响行数
     */
    @Override
    public int delete(int id) {
        return updateByOneParam(id, SQL_DELETE);
    }

    /**
     * 确认取件
     *
     * @param code 取件码
     * @return 影响行数
     */
    @Override
    public int updateState(String code) {
        return updateByOneParam(code, SQL_UPDATE_STATE);
    }

    /**
     * 根据 id 查询快递信息
     *
     * @param id 单号
     * @return 快递信息
     */
    @Override
    public Express getExpressById(int id) {
        return getExpress(id, SQL_GET_EXPRESS_BY_ID);
    }

    /**
     * 根据电话号码关联的快递量按降序获取特定时间内电话号码的集合
     *
     * @return 电话号码的集合
     */
    @Override
    public List<Object[]> getExpressRankByPhoneRangeOfIntime(String timeFrom, String timeTo) {
        List<Object[]> result = new ArrayList<>();
        try {
            if (timeFrom == null || timeTo == null) {
                query(pps(SQL_GET_RANK_OF_COUNT_BY_PHONE), null);
            } else {
                List<Object> params = new ArrayList<>();
                params.add(timeFrom);
                params.add(timeTo);
                query(pps(SQL_GET_RANK_RANGE), params);
            }
            while (rs.next()) {
                Object[] o = new Object[2];
                o[0] = rs.getInt(2);
                o[1] = rs.getString(1);
                result.add(o);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closeAll();
        }
        return result;
    }


    private int updateByOneParam(Object param, String sqlUpdate) {
        try {
            List<Object> p = new ArrayList<>();
            p.add(param);
            return update(getConnection().prepareStatement(sqlUpdate), p);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closeAll();
        }
        return -1;
    }
}
